#coding: utf8
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText
from email.MIMEImage import MIMEImage
import smtplib
import datetime
import sys
import os.path
import logging.config
import MySQLdb
import time
from decimal import *
from email.header import Header
import re, htmlentitydefs
from ConfigParser import SafeConfigParser

class DatabaseDetails(object):
    mode = ""
    host = ""
    user = ""
    passw = ""
    skema = ""
    ctModeLocal = 'db_local'
    ctModeRemote = 'db_remote'

    #file = "C:/BUSINESS/fresh-buzzing/python/fresh/fresh/iRestauranting.properties"
    #class called from scrapy framework but also directly from python, different way to relatively refer to a file. in future pass file parameter in
    file = "../fresh/iRestauranting.properties"
    file2 = "./iRestauranting.properties"
    
    def __init__(self,log):
        try:
            parser = SafeConfigParser()
            
            if os.path.isfile(self.file) == True:
                parser.read(self.file)
            elif os.path.isfile(self.file2) == True:
                parser.read(self.file2)
            else:
                log.msg("Error no se encuetra fichero de configuracion de base de dats " + file,level=log.ERROR)
                return          

            self.mode = parser.get('db','mode')
            self.mode = str(self.mode).strip()
            self.host = parser.get(self.mode,'host')
            self.user = parser.get(self.mode,'user')
            self.passw = parser.get(self.mode,'passw')
            self.skema = parser.get(self.mode,'skema')
            print "mode: " + str(self.mode) + "host: " + str(self.host) + "user: " + str(self.user) + "passw: " + str(self.passw) + "skema: " + str(self.skema)
            
        except Exception,e:
            log.error("Error en leyendo propiedades fichero: " + file + str(e))
        
  

class AttrMail(object):
    strFrom=None
    lTo=None
    strSubject=None
    strMsgASCII=None
    strMsgHTML=None
    strHTMLFile=None
    """doble lista de imagenes y IDs relacionados en el html"""
    listImages=[]
    strSMTP=None
    strUser=None
    strPwd=None

    def __str__( self ):
        return str("toString no implementado")

class SubscriptionRestaurant(object):
    id_subs=None
    id_Rest=None
    rest_name=None
    email=None
    score=None
    score_presence=None
    score_opinions=None
    score_activity=None
    numNewPosts=0
    numDays=0
    post1_net=None
    post1_title=None
    post1_date=None
    post1_score=None
    post1_text=None
    post2_net=None
    post2_title=None
    post2_date=None
    post2_score=None
    post2_text=None
    #dictionary with network and last post
    dictLastPostsNet=None
    firstDelivery=None
    

    def __str__( self ):
        return str("id_Rest:"+str(self.id_Rest)+"self.rest_name:"+self.rest_name+"self.email:"+self.email +\
                   "self.score:"+str(self.score)+"self.score_presence:"+str(self.score_presence)+"self.score_opinions:"+str(self.score_opinions) +\
                   "self.score_activity:"+str(self.score_activity)+"self.numNewPosts:"+str(self.numNewPosts)+"self.post1_net:"+str(self.post1_net) +\
                   "self.post1_title:"+str(self.post1_title)+"self.post1_date:"+str(self.post1_date)+"self.post1_score:"+str(self.post1_score) +\
                   "self.post1_text:"+str(self.post1_text)+"self.post2_net:"+str(self.post2_net)+"self.post2_title:"+str(self.post2_title) +\
                   "self.post2_date:"+str(self.post2_date)+"self.post2_score:"+str(self.post2_score)+"self.post2_text:"+self.post2_text)


class DB(object):
    """
    host = "mysql1.alwaysdata.com"
    user = "62465_jmgost"
    passw = "sygnium"
    skema = "freshbuzzing_1"
    """

    log=None
    
    def __init__(self, log):
        self.log = log

    def connectDB(self):
        try:
            props = DatabaseDetails(self.log)
            self.db = MySQLdb.connect(props.host,props.user,props.passw,props.skema)
            self.db.set_character_set('utf8')
        except Exception,e:
            self.log.error("Error al conectar a base de datos MySQLdb.connect: " + str(e))
            return False
        return True


    def disconnectDB(self):
        self.db
        self.db.close()
        
        
    def getCursor(self):
        try:
            cursor = self.db.cursor()
            cursor.execute('SET NAMES utf8;')
            cursor.execute('SET CHARACTER SET utf8;')
            cursor.execute('SET character_set_connection=utf8;')
            return cursor
        except Exception,e:
            self.log.error("Error al crear cursor MySQLdb:  " + str(e))


    def getSubscriptionsDetails(self):
        list=[]
        curRest=None
        curCTL=None
        curHIST=None

        try:
            #GET ALL RESTAURANTS THAT NEED TO DELIVER SUBSCRIPTION
            sql = "SELECT rs.ID_RESTAURANT,rs.DS_EMAIL,rs.ID_SUBS,ms.NUM_DAYS_PERIODICITY,IF(rs.DT_LAST_SENT is null,'FIRST','OTHER') FROM REL_SUBSCRIPTION_REST rs, MAST_SUBSCRIPTIONS ms \
                  where rs.ID_SUBS=ms.ID_SUBS and \
                  ms.NUM_DAYS_PERIODICITY <= DATEDIFF(NOW(),IFNULL(rs.DT_LAST_SENT,DATE_SUB(NOW(), INTERVAL 30 DAY))) and \
                  BL_ACTIVE='Y'"
            curSubs = self.getCursor()
            curSubs.execute(sql)
            for rowSubs in curSubs.fetchall():
                subsRest = SubscriptionRestaurant()
                subsRest.id_Rest=rowSubs[0]
                subsRest.email=rowSubs[1]
                subsRest.id_subs=rowSubs[2]
                subsRest.numDays=rowSubs[3]
                if rowSubs[4]=='FIRST':
                    subsRest.firstDelivery=True
                else:
                    subsRest.firstDelivery=False
                subsRest.dictLastPostsNet=dict()

                #get basic data from restaurant
                sql2 = "SELECT mrest.DS_REST_NAME, h.NUM_SCORE_GLOBAL,h.NUM_SCORE_PRESENCE, h.NUM_SCORE_OPINIONS, h.NUM_SCORE_ACTIVITY \
                        FROM MAST_RESTAURANTS mrest,HIST_SCORE_GLOBAL h \
                        WHERE mrest.ID_RESTAURANT=h.ID_RESTAURANT and h.ID_RESTAURANT=" +str(subsRest.id_Rest)+" and h.DT_SCORE = (select max(DT_SCORE) from HIST_SCORE_GLOBAL h1 where h1.ID_RESTAURANT=h.ID_RESTAURANT)"
                curRest = self.getCursor()
                curRest.execute(sql2)
                data=curRest.fetchone()
                if data is not None:
                    subsRest.rest_name=data[0]
                    subsRest.score=data[1]
                    subsRest.score_presence=data[2]
                    subsRest.score_opinions=data[3]
                    subsRest.score_activity=data[4]

                    #for each restaurant get id_last_post per web
                    sql3 = "(SELECT  COUNT(*),'ATRAPALO' as CHANNEL, IFNULL(ID_LAST_POST, (SELECT min(id_post)-1 FROM HIST_POSTS_ATRAPALO where dt_post >= DATE_SUB(NOW(), INTERVAL 30 DAY) and ID_RESTAURANT="+str(subsRest.id_Rest)+")) FROM CTL_SUBSCRIPTION_REST_WEB WHERE ID_SUBS=" + str(subsRest.id_subs) +" and ID_WEB_REVIEWS='ATRAPALO' and ID_RESTAURANT=" + str(subsRest.id_Rest) +" )\
                      UNION (SELECT  COUNT(*),'SALIR' as CHANNEL, IFNULL(ID_LAST_POST, (SELECT min(id_post)-1 FROM HIST_POSTS_SALIR where dt_post >= DATE_SUB(NOW(), INTERVAL 30 DAY) and ID_RESTAURANT="+str(subsRest.id_Rest)+")) FROM CTL_SUBSCRIPTION_REST_WEB WHERE ID_SUBS=" + str(subsRest.id_subs) +" and ID_WEB_REVIEWS='SALIR' and ID_RESTAURANT=" + str(subsRest.id_Rest) +") \
                      UNION (SELECT  COUNT(*),'YELP' as CHANNEL, IFNULL(ID_LAST_POST, (SELECT min(id_post)-1 FROM HIST_POSTS_YELP where dt_post >= DATE_SUB(NOW(), INTERVAL 30 DAY) and ID_RESTAURANT="+str(subsRest.id_Rest)+")) FROM CTL_SUBSCRIPTION_REST_WEB WHERE ID_SUBS=" + str(subsRest.id_subs) +" and ID_WEB_REVIEWS='YELP' and ID_RESTAURANT=" + str(subsRest.id_Rest) +") \
                      UNION (SELECT  COUNT(*),'RESTALO' as CHANNEL, IFNULL(ID_LAST_POST, (SELECT min(id_post)-1 FROM HIST_POSTS_RESTALO where dt_post >= DATE_SUB(NOW(), INTERVAL 30 DAY) and ID_RESTAURANT="+str(subsRest.id_Rest)+")) FROM CTL_SUBSCRIPTION_REST_WEB WHERE ID_SUBS=" + str(subsRest.id_subs) +" and ID_WEB_REVIEWS='RESTALO' and ID_RESTAURANT=" + str(subsRest.id_Rest) +") \
                      UNION (SELECT  COUNT(*),'TENEDOR' as CHANNEL, IFNULL(ID_LAST_POST, (SELECT min(id_post)-1 FROM HIST_POSTS_TENEDOR where dt_post >= DATE_SUB(NOW(), INTERVAL 30 DAY) and ID_RESTAURANT="+str(subsRest.id_Rest)+")) FROM CTL_SUBSCRIPTION_REST_WEB WHERE ID_SUBS=" + str(subsRest.id_subs) +" and ID_WEB_REVIEWS='TENEDOR' and ID_RESTAURANT=" + str(subsRest.id_Rest) +") \
                      UNION (SELECT  COUNT(*),'TRIPADVISOR' as CHANNEL, IFNULL(ID_LAST_POST, (SELECT min(id_post)-1 FROM HIST_POSTS_TRIPADVISOR where dt_post >= DATE_SUB(NOW(), INTERVAL 30 DAY) and ID_RESTAURANT="+str(subsRest.id_Rest)+")) FROM CTL_SUBSCRIPTION_REST_WEB WHERE ID_SUBS=" + str(subsRest.id_subs) +" and ID_WEB_REVIEWS='TRIPADVISOR' and ID_RESTAURANT=" + str(subsRest.id_Rest)+")"
                    curCTL = self.getCursor()
                    curCTL.execute(sql3)
                    sql4=""
                    bFirst=True
                    sqlTmp=""
                    for rowCTL in curCTL.fetchall():
                        #in order to make it easy build seperatly each sql
                        net=str(rowCTL[1]).strip()
                        if rowCTL[2] is not None:
                            if net=='TRIPADVISOR':
                                sqlTmp= " (select 'TRIPADVISOR' as CHANNEL, (select count(*)  FROM HIST_POSTS_TRIPADVISOR where ID_POST>" + str(rowCTL[2]) + " and ID_RESTAURANT="+str(subsRest.id_Rest) +") as total ,h.ID_POST, h.DT_POST, h.NUM_SCORE*2, h.DS_TITLE_POST, h.DS_POST \
                                FROM HIST_POSTS_TRIPADVISOR h where h.ID_RESTAURANT="+str(subsRest.id_Rest)+" and ID_POST > "+ str(rowCTL[2]) + " order by h.ID_POST desc limit 2) "
                            elif net=='ATRAPALO':
                                sqlTmp= " (select 'ATRAPALO' as CHANNEL, (select count(*)  FROM HIST_POSTS_ATRAPALO where ID_POST>" + str(rowCTL[2]) + " and ID_RESTAURANT="+str(subsRest.id_Rest) +") as total ,h.ID_POST, h.DT_POST, h.NUM_SCORE, NULL as DS_TITLE_POST, h.DS_POST \
                                FROM HIST_POSTS_ATRAPALO h where h.ID_RESTAURANT="+str(subsRest.id_Rest)+" and h.ID_POST > "+ str(rowCTL[2]) + " order by h.ID_POST desc limit 2) "
                            elif net=='SALIR':
                                sqlTmp= " (select 'SALIR' as CHANNEL, (select count(*)  FROM HIST_POSTS_SALIR where ID_POST>" + str(rowCTL[2]) + " and ID_RESTAURANT="+str(subsRest.id_Rest) +") as total ,h.ID_POST, h.DT_POST, h.NUM_SCORE*2, h.DS_TITLE_POST, h.DS_POST \
                                FROM HIST_POSTS_SALIR h where h.ID_RESTAURANT="+str(subsRest.id_Rest)+" and h.ID_POST > "+ str(rowCTL[2]) + " order by h.ID_POST desc limit 2) "                            
                            elif net=='YELP':
                                sqlTmp= " (select 'YELP' as CHANNEL, (select count(*)  FROM HIST_POSTS_YELP where ID_POST >" + str(rowCTL[2]) + " and ID_RESTAURANT="+str(subsRest.id_Rest) +") as total ,h.ID_POST, h.DT_POST, h.NUM_SCORE*2, NULL as DS_TITLE_POST, h.DS_POST \
                                FROM HIST_POSTS_YELP h where h.ID_RESTAURANT="+str(subsRest.id_Rest)+" and h.ID_POST > "+ str(rowCTL[2]) + " order by h.ID_POST desc limit 2) "                            
                            elif net=='RESTALO':
                                sqlTmp= " (select 'RESTALO' as CHANNEL, (select count(*)  FROM HIST_POSTS_RESTALO where ID_POST>" + str(rowCTL[2]) + " and ID_RESTAURANT="+str(subsRest.id_Rest) +") as total ,h.ID_POST, h.DT_POST, h.NUM_SCORE, NULL as DS_TITLE_POST, h.DS_POST \
                                FROM HIST_POSTS_RESTALO h where h.ID_RESTAURANT="+str(subsRest.id_Rest)+" and h.ID_POST > "+ str(rowCTL[2]) + " order by h.ID_POST desc limit 2) "
                            elif net=='TENEDOR':
                                sqlTmp= " (select 'TENEDOR' as CHANNEL, (select count(*)  FROM HIST_POSTS_TENEDOR where ID_POST>" + str(rowCTL[2]) + " and ID_RESTAURANT="+str(subsRest.id_Rest) +") as total ,h.ID_POST, h.DT_POST, h.NUM_SCORE, NULL as DS_TITLE_POST, h.DS_POST \
                                FROM HIST_POSTS_TENEDOR h where h.ID_RESTAURANT="+str(subsRest.id_Rest)+" and h.ID_POST > "+ str(rowCTL[2]) + " order by h.ID_POST desc limit 2) "                            
                            else:
                                self.log.error("Posbile error de programacion, no se encuentra red: "+net)
                                              
                            if bFirst==True:
                                sql4=sql4 + sqlTmp
                                bFirst=False
                            else:
                                sql4=sql4 + "UNION " + sqlTmp

                    #Execute SQL4 to get posts + total posts per network
                    if len(sql4) > 0:
                        #print(sql4)
                        curHIST = self.getCursor()
                        curHIST.execute(sql4)
                        lastNetWork=""
                        count=0
                        for rowHIST in curHIST.fetchall():
                            #just count if network changed otherwise would count twice by mistake
                            if rowHIST[1] is not None and rowHIST[1]>0:
                                    net=str(rowHIST[0]).strip()
                                    netPosts=int(rowHIST[1])
                                    #update network pointer, just keep the biggest
                                    id_post=int(rowHIST[2])
                                    if subsRest.dictLastPostsNet.get(net) is not None:
                                        if subsRest.dictLastPostsNet[net]<id_post:
                                            subsRest.dictLastPostsNet[net]=id_post
                                    else:
                                        subsRest.dictLastPostsNet[net]=id_post
                                        
                                    if net != lastNetWork:
                                        subsRest.numNewPosts=subsRest.numNewPosts+netPosts
                                    lastNetWork=net
                                    
                                    if count==0:
                                        subsRest.post1_net=rowHIST[0]
                                        subsRest.post1_date=rowHIST[3]
                                        subsRest.post1_score=rowHIST[4]
                                        subsRest.post1_title=rowHIST[5]
                                        subsRest.post1_text=rowHIST[6]
                                    else:
                                        subsRest.post2_net=rowHIST[0]
                                        subsRest.post2_date=rowHIST[3]
                                        subsRest.post2_score=rowHIST[4]
                                        subsRest.post2_title=rowHIST[5]
                                        subsRest.post2_text=rowHIST[6]
                                    count=(count+1)%2
                    else:
                        self.log.error("No hay nuevos posts para restaurante: " + subsRest.rest_name + " id_rest="+str(subsRest.id_Rest))
                else:
                    self.log.error("Posbile error de programacion, no se encuentra restaurante: "+str(subsRest.id_Rest)+ " para obtener ultimos valores globales")
                list.append(subsRest)
                #self.log.debug("Subscription:"+str(subsRest))
                self.db.commit()                           
        except Exception,e:
            self.log.error("Error al ejecutar sentencia SQL en getSubscriptionsDetails:  " + str(e))
            self.db.rollback()
        else:
            self.db.commit()
        finally:
            if curRest is not None:
                curRest.close()
            if curCTL is not None:
                curCTL.close()
            if curHIST is not None:
                curHIST.close()                
            return list

    def subscriptionSent(self,subsRest):

        cur1=None
        cur2=None
        sql=""
        try:
            #update `REL_SUBSCRIPTION_REST`
            sql="UPDATE REL_SUBSCRIPTION_REST SET DT_LAST_SENT=NOW() WHERE ID_SUBS='"+str(subsRest.id_subs)+"' and ID_RESTAURANT='"+str(subsRest.id_Rest)+"' and DS_EMAIL='"+str(subsRest.email)+"'"
            cur1=self.getCursor()
            cur1.execute(sql)
                
            #update ctl_subscription for each network
            cur2=self.getCursor()
            for net,idPost  in subsRest.dictLastPostsNet.items():
                sql="INSERT INTO CTL_SUBSCRIPTION_REST_WEB (ID_SUBS, ID_WEB_REVIEWS, ID_RESTAURANT, ID_LAST_POST, DT_SENT) VALUES ('"+str(subsRest.id_subs)+ "','" + str(net) +"','"+str(subsRest.id_Rest)+"','"+str(idPost)+"',NOW()) \
                     ON DUPLICATE KEY UPDATE ID_LAST_POST='"+str(idPost)+"', DT_SENT=NOW()"
                cur2.execute(sql)
                
        except Exception,e:
            self.log.error("Error al ejecutar sentencia SQL:" + sql+" en subscriptionSent:  " + str(e))
            self.db.rollback()
        else:
            self.db.commit()
        finally:
            if cur1 is not None:
                cur1.close()
            if cur2 is not None:
                cur2.close()   
                

    def __del__(self):
        self.db.close()


class EmailSubscription(object):


    log=None
   
    def __init__(self, log):
        self.log = log

    def prepareEmail(self,l):
        attrMail=None
        try:
            if l.numNewPosts is None or l.numNewPosts == 0:
                return
            
            attrMail = AttrMail()
            attrMail.strFrom='irestauranting@irestauranting.es'
            attrMail.lTo=[l.email]
            #attrMail.lTo=['pcocko@sygnium.com','jmgost@sygnium.com','pcocko@gmail.com','jmgost@gmail.com','pcocko@adasasistemas.com']
            #attrMail.lTo=['jmgost@gmail.com','pcocko@gmail.com']
            #attrMail.lTo=['jmgost@gmail.com','jmgost@sygnium.com','pcocko@gmail.com','pcocko@sygnium.com']
            attrMail.strSMTP='smtp.alwaysdata.com'
            attrMail.strUser='irestauranting@irestauranting.es'
            attrMail.strPwd='sygnium2013'
    
            attrMail=self.__prepareEmailDetail(attrMail,l)
                

        except Exception,e:
            self.log.error("Error prepareEmail:  " + str(e) + "attributes: "+str(l))
            attrMail = None
        finally:
            return attrMail

    def __prepareEmailDetail(self,attrMail,l):
        try:
            scoreT= '%.1f' % Decimal(l.score)
            scorePR= '%.1f' % Decimal(l.score_presence)
            scoreOP= '%.1f' % Decimal(l.score_opinions)
            scoreAC= '%.1f' % Decimal(l.score_activity)
            
            #scoring de los posts
            SCORE_MIN = 6
            SCORE_MAX = 7.5
            dictNet={'ATRAPALO':'atrapalo.png','SALIR':'salir.png','YELP':'yelp.png','TRIPADVISOR':'trip.png','RESTALO':'restalo.png','TENEDOR':'tenedor.png'}
            dictFace={'RED':'face_red.png','ORANGE':'face_orange.png','GREEN':'face_green.png'}
            color=None

            if l.post1_score<SCORE_MIN:
                post1Face='RED'
                post1TitleStyle='color:#ff0000;'
            elif l.post1_score>SCORE_MAX:
                post1Face='GREEN'
                post1TitleStyle='color:#008000;'
            else:
                post1Face='ORANGE'
                post1TitleStyle='color:#fbb040;'

            if l.post2_score is not None:
                if l.post2_score<SCORE_MIN:
                    post2Face='RED'
                    post2TitleStyle='color:#ff0000;'
                elif l.post2_score>SCORE_MAX:
                    post2Face='GREEN'
                    post2TitleStyle='color:#008000;'
                else:
                    post2Face='ORANGE'
                    post2TitleStyle='color:#fbb040;'


            strPosts= '%.0f' % Decimal(l.numNewPosts)
            strDays='%.0f' % Decimal(l.numDays)
            if l.firstDelivery==True:
                attrMail.strSubject=l.rest_name + ' tiene ' + str(strPosts) + ' nuevas opiniones'
            else:
                attrMail.strSubject=l.rest_name + ' ha tenido ' + str(strPosts) + ' nuevas opiniones en los últimos ' + str(strDays) +' días'
                
            hlink = 'http://irestauranting.com/home_resto.php?id_resto=%s&mail=1&camp=%s&intervalo=30d' % (l.id_Rest,99)

            fp = open('templateHeader.html', 'r')
            attrMail.strMsgHTML = fp.read()
            fp.close()
            if l.numNewPosts>1:
                fp = open('templatePost2.html', 'r')
                attrMail.strMsgHTML = attrMail.strMsgHTML + fp.read()
                fp.close()

            fp = open('templateFooter.html', 'r')
            attrMail.strMsgHTML = attrMail.strMsgHTML + fp.read()
            fp.close()
            
            strH1= 'iRestauranting ha detectado nuevas opiniones del restaurante <span> <a style="color:#ff8c00;text-decoration:none" target="_blank" href=%s> %s </a> </span>' % (hlink,str(l.rest_name))
            
            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$h1$',strH1)
            if l.post1_title is None:
                strTitle1=''
            else:
                strTitle1 = ' <div style="margin-bottom:5px;"> <span  style="font-size:18px;font-weight:bold;%s;text-decoration: none;">%s</span> </div>' %(post1TitleStyle,str(l.post1_title))
                
            if l.post2_title is None or len(l.post2_title)==0:
                strTitle2=''
            else:
                strTitle2 = ' <div style="margin-bottom:5px;"> <span  style="font-size:18px;font-weight:bold;%s;text-decoration: none;">%s</span> </div>' %(post2TitleStyle,str(l.post2_title))
                
            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$post1_title$',strTitle1)
            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$post2_title$',strTitle2)

            #repetition of template1
            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$rest_name$',l.rest_name)
            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$score_t$',scoreT)
            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$score_p$',scorePR)
            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$score_o$',scoreOP)
            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$score_a$',scoreAC)

            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$post1_date$',l.post1_date.strftime('%d-%m-%Y'))
            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$post1_text$',str(l.post1_text))
            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$post1_stars$',self.__getHtmlStars(l.post1_score))
            if l.post2_text is not None:
                attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$post2_date$',l.post2_date.strftime('%d-%m-%Y'))
                attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$post2_text$',str(l.post2_text))
                attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$post2_stars$',self.__getHtmlStars(l.post2_score))
            attrMail.strMsgHTML=attrMail.strMsgHTML.replace('$irest_link$',hlink)

            #añadir lista para imagenes
            #attrMail.listImages=[['LogoHome.png','<LogoHome>'],['BotonC2.png','<Boton>'],[dictNet[l.post1_net],'<ImgNet1>'], [dictNet[l.post2_net],'<ImgNet2>'],['star_full.png','<StarFull>'],['star_mid.png','<StarMid>'],['star_empty.png','<StarEmpty>']]
            attrMail.listImages=[['LogoHome.png','<LogoHome>'],['BotonC2.png','<Boton>'],[dictNet[l.post1_net],'<ImgNet1>'], ['star_full.png','<StarFull>'],['star_mid.png','<StarMid>'],['star_empty.png','<StarEmpty>']]
            if l.post2_net is not None:
                attrMail.listImages.append([dictNet[l.post2_net],'<ImgNet2>'])

            fp = open('template.txt', 'r')
            attrMail.strMsgASCII = fp.read()
            fp.close()
            attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$rest_name$',str(l.rest_name))
            attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$post1_date$',l.post1_date.strftime('%d-%m-%Y'))
            attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$post1_net$',l.post1_net.title())
            attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$post1_text$',str(l.post1_text))
            
            if l.post2_text is not None:
                attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$post2_date$',l.post2_date.strftime('%d-%m-%Y'))
                attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$post2_net$',l.post2_net.title())
                attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$post2_text$',str(l.post2_text))
            attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$irest_link$',hlink)

            #repetition of template1
            attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$score_t$',scoreT)
            attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$score_p$',scorePR)
            attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$score_o$',scoreOP)
            attrMail.strMsgASCII=attrMail.strMsgASCII.replace('$score_a$',scoreAC)
                               
        except Exception,e:
            self.log.error("Error prepare Email2:  " + str(e) + "attributes: "+str(l))
            attrMail = None
        finally:
            return attrMail


    def __getHtmlStars(self,score):

        html = ''
        cont_star = 0

        while(score > 0):
            if(score >= 1):
                html = html + '<img width="15" height="15" src="cid:StarFull" />'
            else:
                html = html + '<img width="15" height="15" src="cid:StarMid" />'
            score = score - 2
            cont_star=cont_star+1

        while(cont_star < 5):
            html = html + '<img width="15" height="15" src="cid:StarEmpty" />';
            cont_star=cont_star+1


        return html
    
    def sendEmail(self,attrMail):
        try:
            # Create the root message and fill in the from, to, and subject headers
            msgRoot = MIMEMultipart('related')
            msgRoot['Subject'] = Header(attrMail.strSubject.encode('utf-8'),"utf-8")
            msgRoot['From'] = attrMail.strFrom
            msgRoot['To'] = ",".join(attrMail.lTo) 
            msgRoot.preamble = 'This is a multi-part message in MIME format.'

            msgAlternative = MIMEMultipart('alternative')
            msgRoot.attach(msgAlternative)

            msgText = MIMEText(attrMail.strMsgASCII.encode('utf-8'),'plain','UTF-8')
            msgAlternative.attach(msgText)

            # We reference the image in the IMG SRC attribute by the ID we give it below
            msgText = MIMEText(attrMail.strMsgHTML.encode('utf-8'), 'html','UTF-8')
            msgAlternative.attach(msgText)

            # This example assumes the image is in the current directory
            for i in attrMail.listImages:
                fp = open(i[0], 'rb')
                msgImage = MIMEImage(fp.read())
                fp.close()
                msgImage.add_header('Content-ID', i[1])
                msgRoot.attach(msgImage)


            # Send the email (this example assumes SMTP authentication is required)
            smtp = smtplib.SMTP()
            smtp.connect(attrMail.strSMTP)
            smtp.login(attrMail.strUser, attrMail.strPwd)
            smtp.sendmail(attrMail.strFrom, attrMail.lTo, msgRoot.as_string())
            smtp.quit()
            return True
        except Exception,e:
            self.log.error("Error sendEmail:  " + str(e) + "attributes: " + str(attrMail))
            return False
        

def __init(log):
    try:
        reload(sys)
        sys.setdefaultencoding('utf-8')
        time = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
        filename = time + '.log'
        filename = os.path.join('../logEmailSubscription/',filename)
        handler = logging.FileHandler(filename)
        handler2 = logging.StreamHandler(sys.stdout)
        # create a logging format
        formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
        handler.setFormatter(formatter)
        handler2.setFormatter(formatter)
        handler.setLevel(logging.DEBUG)
        handler2.setLevel(logging.DEBUG)

        # add the handlers to the logger
        log = logging.getLogger('EmailMarketing')
        log.setLevel(logging.DEBUG)
        log.addHandler(handler)
        log.addHandler(handler2)


        return log
    except Exception,e:
        print "Error en __init: " + str(e)     

def main():
    numEmails=0
    log=None
    db=None
    attrMail=None
    
    try:
        log = __init(log)
        db = DB(log)
        db.connectDB()
        
        
        emailSubscription = EmailSubscription(log)

        lSubscriptionRest = db.getSubscriptionsDetails()
     
        for l in lSubscriptionRest:
            log.debug("Inicio envio subscripciones.....")
            attrMail = emailSubscription.prepareEmail(l)
            if attrMail is not None:
                if emailSubscription.sendEmail(attrMail):
                    log.info("Email mandado correctamente para restaurante: %s, email: %s",l.rest_name,l.email)
                    numEmails =numEmails +1
                    db.subscriptionSent(l)
                else:
                    log.error("Error al intentar mandar mandar email para restaurante: %s, email: %s", l.rest_name,l.email)   
            else:
                log.error("No se ha podido rellenar email para restaurante: %s, email: %s", l.rest_name,l.email)
            

        log.info("Total emails: %s",str(numEmails))
        

    except Exception,e:
        log.error("Error en Main: " + str(e))
    finally:
        return



if __name__ == "__main__":
    main() 
